package com.esun.common.utils;

import com.esun.payment_api.domain.TExcelTableCol;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel操作工具类
 */
public class ExcelUtils {
    /**
     * 对单元格中数值进行格式化
     */
    public static Object getCellValue(Cell cell) {
        Object value = null;
        //格式化字符类型的数字
        DecimalFormat df = new DecimalFormat("0");
        //日期格式化
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");
        //格式化数字
//        DecimalFormat df2 = new DecimalFormat("0.00");
        DecimalFormat df2 = new DecimalFormat("0");
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                        value = df.format(cell.getNumericCellValue());
                    } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                        value = sdf.format(cell.getDateCellValue());
                    } else {
                        value = df2.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_FORMULA: //公式类型
                    try {
                        /**
                         * 获取公式名称，如A1+B1：
                         * value = cell.getCellFormula();
                         * 这样对于字符串cell.getStringCellValue()方法即可取得其值，
                         * 如果公式生成的是数值，使用cell.getStringCellValue()方法，会抛出IllegalStateException异常，
                         * 在异常处理中使用cell.getNumericCellValue();
                         */
                        value = String.valueOf(cell.getStringCellValue());
                    } catch (IllegalStateException e) {
                        BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                        value = bd.setScale(2, BigDecimal.ROUND_DOWN).toPlainString();
                    }
                    System.out.println("公式为：" + cell.getCellFormula() + ",值为：" + value);
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                default:
                    value = "unknow value";
                    break;
            }
        }
        return value;
    }

    /**
     * Excel导入，通用方法
     */
    public static Map<Integer, String> excelImport(MultipartFile file, String tableName, String collectionName, List<TExcelTableCol> excelParams) {
        Map<Integer, String> mapSql = new HashMap<>();
        try {
            //将MultipartFile文件转为Workbook文件
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            //获取公式执行工具
//            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
            //获取sheet
            Sheet sheet = workbook.getSheetAt(excelParams.get(0).getSheetNo());
            //获取总行数
            //int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum()+1;
            int rowCount = sheet.getPhysicalNumberOfRows();
            System.out.println("总行数：" + rowCount);
            //获取标题行，以标题行的列数为准
            Row row0 = sheet.getRow(0);
            //获取不为空的列个数
            int colCount = row0.getPhysicalNumberOfCells();
            //这里是获取配置表中所有配置的个数，区分实际excel的列数，paramCount>=colCount
            int paramCount = excelParams.size();

            //拼接part1：插入表所对应的详细字段
            String sqlPart1 = "";
            sqlPart1 += "(";
            for (int i = 0; i < paramCount; i++) {
                if (i < paramCount - 1) {
                    sqlPart1 += excelParams.get(i).getTableParam();
                    sqlPart1 += ", ";
                } else {
                    sqlPart1 += excelParams.get(i).getTableParam();
                }
            }
            sqlPart1 += ") ";

            //拼接part2：插入字段所对应的数据
            String sqlPart2 = "";
            for (int i = 1; i < rowCount; i++) {
                //获取第i行数据
                Row row = sheet.getRow(i);
                sqlPart2 += collectionName;
                sqlPart2 += "', '";
                for (int k = 0; k < colCount; k++) {
                    if (k < colCount - 1) {
                        sqlPart2 += getCellValue(row.getCell(k));
                        sqlPart2 += "', '";
                    } else {
                        sqlPart2 += getCellValue(row.getCell(k));
                    }
                }
                //拼接完整sql语句
                String sql = "insert into " + tableName + sqlPart1 +
                        "values ('" + sqlPart2 + "')";
                mapSql.put(i, sql.replaceAll("null",""));
                //重置part2
                sqlPart2 = "";
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return mapSql;
    }

}
